/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Date;

/**
 *
 * @author Bhushan
 */
public class DataAccess {

    // Read config pparamters
    public static double[] getConfig() throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        String selStmt = " SELECT * FROM outsourced.config";
        PreparedStatement pstm = null;
        List<Double> config = null;
        Double[] temp = null;
        double[] configArr = null;
        try {
            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(selStmt);
            rs = pstm.executeQuery();
            config = new ArrayList<Double>();
            while (rs.next()) {

                config.add(rs.getDouble("parameters"));
            }
            temp = new Double[config.size()];
            configArr = new double[config.size()];
            config.toArray(temp);

            for (int i = 0; i < config.size(); i++) {

                configArr[i] = temp[i].doubleValue();
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pstm.close();
            conn.close();
            rs.close();
        }

        return configArr;
    }

    // Reads latest effort present in data_table
    public static double[] readEfforts(int no_players) throws SQLException {

        Connection conn = null;
        ResultSet rs = null;
        String selStmt = " SELECT * FROM outsourced.data_table WHERE user_id = ? ORDER BY time_stamp DESC LIMIT 0, 1";
        PreparedStatement pstm = null;
        double[] effortArr = new double[no_players];
        try {
            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(selStmt);
            for (int i = 0; i < no_players; i++) {

                pstm.setInt(1, i);
                rs = pstm.executeQuery();
                if (rs.next()) {
                    effortArr[i] = rs.getDouble("effort");
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            rs.close();
            pstm.close();
        }
        return effortArr;
    }

    // Read tree
    public static int[][] getTree(int no_players) throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        String selStmt = " SELECT * FROM outsourced.tree";
        PreparedStatement pstm = null;
        int[][] temp = new int[no_players][4];

        try {
            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(selStmt);
            rs = pstm.executeQuery();

            int count = 0;
            while (rs.next()) {
                temp[count][0] = rs.getInt("user_id");
                temp[count][1] = rs.getInt("parent");
                temp[count][2] = rs.getInt("is_leaf");
                temp[count][3] = rs.getInt("order_DFS");
                count++;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            pstm.close();
            conn.close();
            rs.close();
        }
        return temp;

    }

    // Saves utility, effort, utility due to subtree and time in DB 
    public static void insertData(int userID, double effort, double utility, double utility_child, int who_changed) throws SQLException {
        Connection conn = null;
        String selStmt = " INSERT INTO outsourced.data_table VALUES(?, ?, ?, ?, ?, ?)";
        PreparedStatement pstm = null;
        try {
            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(selStmt);
            pstm.setInt(1, userID);
            pstm.setDouble(2, effort);
            pstm.setDouble(3, utility);
            pstm.setDouble(4, utility_child);
            Date date = new Date();
            pstm.setDouble(5, (double) date.getTime());
            pstm.setInt(6, who_changed);
            pstm.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            pstm.close();

        }
    }

    // Read utility history for a player
    public static double[][] readDataFromDataTable(int userID) throws SQLException {
        Connection conn = null;
        String selStmt = " SELECT utility, time_stamp FROM data_table where user_id = ? ORDER BY time_stamp DESC LIMIT 0, 30 ";
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Double> data = null;
        List<Double> time = null;
        Double[] temp1 = null;
        Double[] temp2 = null;
        double[][] dataArr = null;

        try {
            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(selStmt);
            pstm.setInt(1, userID);
            rs = pstm.executeQuery();
            data = new ArrayList<Double>();
            time = new ArrayList<Double>();
            while (rs.next()) {
                data.add(rs.getDouble("utility"));
                time.add(rs.getDouble("time_stamp"));
            }

            temp1 = new Double[data.size()];
            temp2 = new Double[time.size()];
            dataArr = new double[data.size()][2];
            data.toArray(temp1);
            time.toArray(temp2);
            int length = data.size();
            for (int i = 0; i < length; i++) {
                dataArr[length - i - 1][0] = (double) temp1[i];   // Y-Axis
                dataArr[length - i - 1][1] = (double) temp2[i];   // X-Axis
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            pstm.close();
            rs.close();
        }
        return dataArr;
    }

    // Reads utility due to subtree
    public static double[][] readSubTreeUtil(int userID) throws SQLException {

        Connection conn = null;
        String selStmt = " SELECT utility_children, time_stamp FROM data_table where user_id = ? ORDER BY time_stamp DESC LIMIT 0, 30 ";
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Double> data = null;
        List<Double> time = null;
        Double[] temp1 = null;
        Double[] temp2 = null;
        double[][] dataArr = null;

        try {
            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(selStmt);
            pstm.setInt(1, userID);
            rs = pstm.executeQuery();
            data = new ArrayList<Double>();
            time = new ArrayList<Double>();
            while (rs.next()) {
                data.add(rs.getDouble("utility_children"));
                time.add(rs.getDouble("time_stamp"));
            }

            temp1 = new Double[data.size()];
            temp2 = new Double[time.size()];
            dataArr = new double[data.size()][2];
            data.toArray(temp1);
            time.toArray(temp2);
            int length = data.size();
            for (int i = 0; i < length; i++) {
                dataArr[length - i - 1][0] = (double) temp1[i];   // Y-Axis
                dataArr[length - i - 1][1] = (double) temp2[i];   // X-Axis
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            pstm.close();
            rs.close();
        }
        return dataArr;


    }

    // read latest effort of player
    public static double getLatestEffortforUser(int userID) throws SQLException {

        Connection conn = null;
        String selStmt = " SELECT effort, time_stamp FROM data_table where user_id = ? AND user_id = who_changed ORDER BY time_stamp DESC LIMIT 0, 1 ";
        PreparedStatement pstm = null;
        ResultSet rs = null;
        double effort = 0;

        try {
            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(selStmt);
            pstm.setInt(1, userID);
            rs = pstm.executeQuery();

            if (rs.next()) {
                effort = rs.getDouble("effort");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            pstm.close();
            rs.close();
        }
        return effort;
    }

    /*
     * Cycles the user control
     */
    public static void cycleUser(int userID) throws SQLException {

        Connection conn = null;
        String updStmt = " UPDATE cycle SET status = ? where user_id = ? ";
        String selStmt = " SELECT * FROM config";
        PreparedStatement pstm = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int no_players = 0;

        try {

            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(updStmt);
            pstm.setInt(1, 0);  // Disable the released user
            pstm.setInt(2, userID);

            stmt = conn.prepareStatement(selStmt);
            rs = stmt.executeQuery();
            int count = 0;
            while (rs.next()) {
                if (count == 4) { // num_players is stored in 5th entry
                    no_players = (int) rs.getDouble(1);
                }
                count++;
            }
            pstm.executeUpdate();
            if (userID == 0) {
                userID = no_players - 1;
            } else {
                userID--;
            }
            pstm.setInt(1, 1);  // Enable the next user
            pstm.setInt(2, userID);
            pstm.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            rs.close();
            pstm.close();
            stmt.close();
        }
    }

    public static boolean checkStatus(int userID) throws SQLException {
        Connection conn = null;
        String selStmt = " SELECT * FROM cycle WHERE user_id = ? ";
        PreparedStatement pstm = null;
        ResultSet rs = null;
        boolean status = false;

        try {

            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(selStmt);
            pstm.setInt(1, userID);
            rs = pstm.executeQuery();
            if (rs.next()) {

                if (rs.getInt("status") == 1) {
                    status = true;
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            pstm.close();
            rs.close();
        }
        return status;
    }

    public static int getActivePlayer() throws SQLException {
        Connection conn = null;
        String selStmt = " SELECT * FROM cycle WHERE status = 1 ";
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int user = -1;

        try {

            conn = DataAccess.getConnection(conn);
            pstm = conn.prepareStatement(selStmt);
            rs = pstm.executeQuery();
            if (rs.next()) {

                user = rs.getInt("user_id");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            pstm.close();
            rs.close();
        }
        return user;
    }

    public static Connection getConnection(Connection conn) throws Exception {
        Properties connectionProps = null;

        try {

            connectionProps = new Properties();
            connectionProps.put("user", "root");
            connectionProps.put("password", "root");
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(
                    "jdbc:Mysql://localhost:3306/outsourced", connectionProps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}
